﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using QLTB.Entities;
using QLTB.Data;
using _4rumDCT.App_Code;

namespace QuanLyThietBi.DCTEDU_.QuanTri
{
    public partial class QLBangDiem : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void grdTrades_PageIndexChanging(Object sender, GridViewPageEventArgs e)
        {
            gvExcelFile.PageIndex = e.NewPageIndex;
            gvExcelFile.DataBind();
        }

        protected void btnUpload_Click(object sender, EventArgs e)
        {
            //Coneection String by default empty  
            string ConStr = "";
            //Extantion of the file upload control saving into ext because   
            //there are two types of extation .xls and .xlsx of Excel   
            string ext = Path.GetExtension(FileUpload1.FileName).ToLower();
            //getting the path of the file   
            string path = Server.MapPath("~/Upload/TepTin/" + FileUpload1.FileName);
            //saving the file inside the MyFolder of the server  
            FileUpload1.SaveAs(path);
            lblFileDinhKem.Text = FileUpload1.FileName + "\'s đã được đưa vào trong bộ nhớ tạm và chờ cập nhật vào CSDL";
            //checking that extantion is .xls or .xlsx  
            if (ext.Trim() == ".xls")
            {
                //connection string for that file which extantion is .xls  
                ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            }
            else if (ext.Trim() == ".xlsx")
            {
                //connection string for that file which extantion is .xlsx  
                ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }
            //making query  

            string[] file_name = FileUpload1.FileName.Split('.');
            H_MaLopHP.Value = file_name[0];
            string query = "SELECT * FROM [" + file_name[0] + "$]";
            //Providing connection  
            OleDbConnection conn = new OleDbConnection(ConStr);
            //checking that connection state is closed or not if closed the   
            //open the connection  
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            //create command object  
            OleDbCommand cmd = new OleDbCommand(query, conn);
            // create a data adapter and get the data into dataadapter  
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            //fill the Excel data to data set  
            da.Fill(ds);
            //set data source of the grid view  
            gvExcelFile.DataSource = ds.Tables[0];
            //binding the gridview  
            gvExcelFile.DataBind();
            //close the connection  
            conn.Close();

            //string sLinkDownLoadPath = Server.MapPath(Common.sPathUpFile);
            //if (!Directory.Exists(sLinkDownLoadPath)) Directory.CreateDirectory(sLinkDownLoadPath);
            //if (File.Exists(sLinkDownLoadPath + "\\" + args.FileName))
            //{
            //    File.Delete(sLinkDownLoadPath + "\\" + args.FileName);
            //}
            //if (File.Exists(sLinkDownLoadPath + "\\" + H_FileChonLai.Value))
            //{
            //    File.Delete(sLinkDownLoadPath + "\\" + H_FileChonLai.Value);
            //}
            //args.MoveTo(sLinkDownLoadPath + "\\" + args.FileName);

            //if (lblFileDinhKem.Text != "")
            //{
            //    lblFileDinhKem.Text = lblFileDinhKem.Text + ";" + args.FileName;
            //}
            //else
            //    lblFileDinhKem.Text = args.FileName;
            //H_FileName.Value = args.FileName;
            //BindGridview();
            cmdSave.Visible = true;

        }

        protected void cmdSave_OnClick(object sender, EventArgs e)
        {
            string[] file_name = FileUpload1.FileName.Split('.');
            int GVCount = gvExcelFile.Rows.Count;
            //litTest.Text = Server.HtmlDecode(gvExcelFile.Rows[5].Cells[1].Text).Trim();
            bool updateStatus = false;
            for (int i = 5; i < GVCount; i++)
            //foreach (GridViewRow GVRow in gvExcelFile.Rows)
            {
                BangDiem ent_BangDiem = new BangDiem();
                ent_BangDiem.MaLopHp = H_MaLopHP.Value;
                ent_BangDiem.MaSinhVien = Server.HtmlDecode(gvExcelFile.Rows[i].Cells[1].Text).Trim();
                ent_BangDiem.Diem1 = Server.HtmlDecode(gvExcelFile.Rows[i].Cells[5].Text).Trim();
                ent_BangDiem.Diem2 = Server.HtmlDecode(gvExcelFile.Rows[i].Cells[6].Text).Trim();
                ent_BangDiem.Diem3 = Server.HtmlDecode(gvExcelFile.Rows[i].Cells[7].Text).Trim();
                ent_BangDiem.Diem4 = Server.HtmlDecode(gvExcelFile.Rows[i].Cells[8].Text).Trim();
                ent_BangDiem.Diem5 = Server.HtmlDecode(gvExcelFile.Rows[i].Cells[9].Text).Trim();
                ent_BangDiem.DiemTkhp = Server.HtmlDecode(gvExcelFile.Rows[i].Cells[10].Text).Trim();
                ent_BangDiem.DiemChu = Server.HtmlDecode(gvExcelFile.Rows[i].Cells[11].Text).Trim();
                ent_BangDiem.NgayCapNhat = Common.GetServerDateTime();
                if (DataRepository.SinhVienProvider.GetByMaSinhVien(ent_BangDiem.MaSinhVien) == null)
                {
                    SinhVien ent_SinhVien = new SinhVien();
                    ent_SinhVien.MaSinhVien = ent_BangDiem.MaSinhVien;
                    ent_SinhVien.HoTen = Server.HtmlDecode(gvExcelFile.Rows[i].Cells[2].Text).Trim() + " " + Server.HtmlDecode(gvExcelFile.Rows[i].Cells[3].Text).Trim();
                    ent_SinhVien.NgayCapNhat = Common.GetServerDateTime();
                    DataRepository.SinhVienProvider.Insert(ent_SinhVien);
                }

                if (DataRepository.LopHocPhanProvider.GetByMaLopHp(ent_BangDiem.MaLopHp) == null)
                {
                    LopHocPhan ent_LopHocPhan = new LopHocPhan();
                    ent_LopHocPhan.MaLopHp = ent_BangDiem.MaLopHp;
                    TList<CanBo> TList_CanBo =
                        DataRepository.CanBoProvider.Find("HoTen like N'%" +
                                                          Server.HtmlDecode(gvExcelFile.Rows[1].Cells[3].Text).Trim() +
                                                          "%'");
                    if (TList_CanBo.Count > 0)
                    {
                        ent_LopHocPhan.MaCanBo = TList_CanBo[0].MaCanBo;
                    }
                    else
                    {
                        ent_LopHocPhan.MaCanBo = "5050065";
                    }
                    ent_LopHocPhan.TenLopHp = Server.HtmlDecode(gvExcelFile.Rows[0].Cells[3].Text).Trim();
                    DataRepository.LopHocPhanProvider.Insert(ent_LopHocPhan);

                }
                int totalRows = 0;
                string whereClause = "MaSinhVien like '%" + ent_BangDiem.MaSinhVien + "%' and MaLopHP like '%" + ent_BangDiem.MaLopHp + "%'";
                TList<BangDiem> TList_BangDiem = DataRepository.BangDiemProvider.GetPaged(whereClause, "", 0, 0,
                    out totalRows);
                if (TList_BangDiem.Count > 0)
                {
                    ent_BangDiem.Id = TList_BangDiem[0].Id;
                    DataRepository.BangDiemProvider.Update(ent_BangDiem);
                }
                else
                {
                    DataRepository.BangDiemProvider.Insert(ent_BangDiem);
                }

            }
            Response.Redirect("~/DCTEDU_/QuanTri/QLBangDiem.aspx");
        }
    }
}